const DB = require('../../utils/db');

module.exports = {
	screenList: async () => {
		// 1 系统数据查询

		const currentDate = new Date();
		const startOfDay = new Date(currentDate.getFullYear(), currentDate.getMonth(), currentDate.getDate());
		const endOfDay = new Date(startOfDay);
		endOfDay.setDate(endOfDay.getDate() + 1);

		// 访问数
		let [visitorTotal] = await DB.query(`select count(1) as visitorTotal from t_visitor `);
		let [visitor] = await DB.query(`select count(1) as visitor from t_visitor where updateTime >= ? AND updateTime < ? `, [startOfDay, endOfDay]);

		// 评论数
		let [commentTotal] = await DB.query(`select count(1) as commentTotal from t_comment `);
		let [comment] = await DB.query(`select count(1) as comment from t_comment where createTime >= ? AND createTime < ? `, [startOfDay, endOfDay]);

		// 留言数
		let [msgTotal] = await DB.query(`select count(1) as msgTotal from t_msg `);
		let [msg] = await DB.query(`select count(1) as msg from t_msg where createTime >= ? AND createTime < ? `, [startOfDay, endOfDay]);

		// 文章数

		let [articleTotal] = await DB.query(`select count(1) as articleTotal from t_acticle `);
		let [article] = await DB.query(`select count(1) as article from t_acticle where create_time >= ? AND create_time < ? `, [startOfDay, endOfDay]);

		// 2 访客列表

		let [visitorList] = await DB.query(`select v.ip,v.province,u.username from t_visitor v left join t_user u on v.userId= u.id where v.type=1`);

		// 3 文章分类

		let [articleList] = await DB.query(`select t.name,c.num value from t_acticle_type t left join  (select type,count(1) as num from t_acticle group by type) c on t.id=c.type`);

		// 4 访客地区分析

		let [visitorArea] = await DB.query(`select province,count(1) as num from t_visitor  group by province order by num desc limit 6`);

		// 5 用户访问量统计
		let [userVisitor] = await DB.query(`select u.username, v.count from t_visitor v left join t_user u on v.userId=u.id  order by count desc limit 6`);

		return {
			sysList: {
				all: [visitorTotal[0].visitorTotal, commentTotal[0].commentTotal, msgTotal[0].msgTotal, articleTotal[0].articleTotal],
				today: [visitor[0].visitor, comment[0].comment, msg[0].msg, article[0].article]
			},
			visitor: {
				list: visitorList
			},
			article: {
				list: articleList
			},
			area: {
				list: visitorArea
			},
			user: {
				list: userVisitor
			}
		};
	}
};
